<?php
///////////////////
//Function commune inventaire
///////////////////
$connection_noa = buildConnection("mysql", "localhost", NULL, $baseNOA, $baseNOA, $mdpNOA); 

///////////////////
//Function commune
///////////////////

/**
 * getIndicateur
 * @param string $p_prefixTable
 * @param int $p_code
 * @param int $p_duree
 * @param \stdClass $p_param
 * @return \stdClass
 */
function buildSituation($p_code, $p_duree, $p_param) {
    global $base;
    global $connection;
    global $connection_noa;
    global $prefixTable;
    
    try {
        $object_retour = new stdClass();
        $object_retour->strErreur = "";
        $object_retour->metro = "";
        $object_retour->data = "";
        
        $object_retour->metro["etape0"] = getDateTimeWithMili();
        
        //On vérifie si donnee stocké est bonne
        $strSql = "SELECT a.*
            , IF(((a.`buildDate` + INTERVAL ".$p_duree." SECOND) > NOW()), true, false) as 'ajour'
            , IF( a.`param` = '".json_encode($p_param)."', true, false) as 'sameParam'
            FROM `".$prefixTable."tab_indicateurs` a
            WHERE 1=1
            AND a.`code` = '".$p_code."'
        ;";

        $req = $connection->prepare($strSql);

        $indicateur = array();
        if($req->execute()){
            $indicateur = $req->fetch();
        }else{
            die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
        }
        $req->closeCursor();
        
        $object_retour->data["recal"] = "ko";
        //Si pas de donnée, ou si pas à jour, ou que les paramètres sont différent.
        if(($indicateur["ajour"] == "0")||($indicateur["sameParam"] == "0")){
            $object_retour->data["recal"] = "ok";
            //--------------------------------------------------------------------------
            $strSql = "UPDATE `".$prefixTable."tab_parametres`
                SET `param_value` = (
                    SELECT DATE_FORMAT(MIN(b.`dateTime_creation`), '%Y-%m-%d') as 'date_min'
                    FROM `".$prefixTable."tab_tags_affecte` b
                    WHERE 1=1
                    AND b.`actif` = 1
                    AND b.`id_type` = 9/*Statut*/
                    AND b.`id_tag` != 5/*Fermé*/
                )
                WHERE 1=1
                AND `param_name` = 'mostOld'
            ;";
            $req = $connection_noa->prepare($strSql);

            if(!$req->execute()){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            $req->closeCursor();

            $object_retour->metro["etape1"] = getDateTimeWithMili();

            //--------------------------------------------------------------------------
            $strSql = "DROP TABLE IF EXISTS `".$base."`.`tmp_data_tickets`;
                CREATE TABLE `".$base."`.`tmp_data_tickets`(
                `id_toc` int(11) NOT NULL,
                `label` varchar(255) NOT NULL,
                `id_prio` int(11) NOT NULL,
                `id_nature` int(11) NOT NULL,
                `version` int(11) NOT NULL,
                INDEX (`id_toc`)
                ) 
                SELECT a.`id` as 'id_toc', a.`label`, c.`id_tag` as 'id_prio', d.`id_tag` as 'id_nature', IFNULL(e.`id_tag`,1) as 'version'
                FROM `".$prefixTable."tab_tickets` a
                , `".$prefixTable."tab_parametres` b
                , `".$prefixTable."tab_tags_affecte` c
                , `".$prefixTable."tab_tags_affecte` d
                LEFT OUTER JOIN `".$prefixTable."tab_tags_affecte` e
                ON d.`id` = e.`id_toc` AND e.`id_type` = 91/*Version*/ AND e.`actif` = 1
                WHERE 1=1
                AND a.`id` = c.`id_toc`
                AND c.`id_type` = 15/*Priorité*/
                AND c.`actif` = 1
                AND a.`id` = d.`id_toc`
                AND d.`id_type` = 28/*Nature*/
                AND d.`actif` = 1
                AND b.`param_name` = 'mostOld'
                AND DATE_FORMAT(a.`dateTime_creation`, '%Y-%m-%d') > STR_TO_DATE(b.`param_value`,'%Y-%m-%d')
                AND DATE_FORMAT(a.`dateTime_creation`, '%Y-%m-%d') <= STR_TO_DATE('".$p_param->date."','%Y-%m-%d')
            ;";
            $req = $connection_noa->prepare($strSql);

            if(!$req->execute()){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            $req->closeCursor();

            $object_retour->metro["etape2"] = getDateTimeWithMili();

            //--------------------------------------------------------------------------
            $strSql = "DROP TABLE IF EXISTS `".$base."`.`tmp_data_status`;
                CREATE TEMPORARY TABLE `".$base."`.`tmp_status_0`(
                        `id_statut` int(11) NOT NULL,
                        `id_toc` int(11) NOT NULL,
                        `statut` tinyint(5) NOT NULL,
                        `dateTime_creation` dateTime,
                        `auteur_creation` varchar(255) NOT NULL,
                        INDEX (`id_toc`)
                )
                SELECT b.`id` as 'id_statut', b.`id_toc`, b.`id_tag` as 'statut', b.`dateTime_creation`, b.`auteur_creation`
                FROM `".$base."`.`tmp_data_tickets` a, `".$prefixTable."tab_tags_affecte` b
                WHERE 1=1
                AND a.`id_toc` = b.`id_toc` 
                AND b.`id_type` = 9/*Statut*/
                AND b.`auteur_creation` = 'synchro'
                ;

                CREATE TEMPORARY TABLE `".$base."`.`tmp_status_1`(
                        `id_toc` int(11) NOT NULL,
                        INDEX (`id_toc`)
                )
                SELECT a.`id_toc`
                FROM `".$base."`.`tmp_data_tickets` a
                WHERE 1=1
                AND NOT EXISTS (
                        SELECT 1
                        FROM `".$base."`.`tmp_status_0` b
                        WHERE 1=1
                        AND a.`id_toc` = b.`id_toc`
                )
                ;

                CREATE TEMPORARY TABLE `".$base."`.`tmp_status_2` AS
                SELECT b.`id` as 'id_statut', b.`id_toc`, b.`id_tag` as 'statut', b.`dateTime_creation`, b.`auteur_creation`
                FROM `".$base."`.`tmp_status_1` a, `".$prefixTable."tab_tags_affecte` b
                WHERE 1=1
                AND a.`id_toc` = b.`id_toc` 
                AND b.`id_type` = 9/*Statut*/
                ;

                CREATE TABLE `".$base."`.`tmp_data_status`(
                        `id_statut` int(11) NOT NULL,
                        `id_toc` int(11) NOT NULL,
                        `statut` int(11) NOT NULL,
                        `dateTime_creation` dateTime,
                        `auteur_creation` varchar(255) NOT NULL,
                        INDEX (`id_toc`),
                        INDEX (`id_statut`),
                        INDEX `statut_auteur` (`statut`,`auteur_creation`)
                )
                SELECT *
                FROM `".$base."`.`tmp_status_0`
                UNION ALL
                SELECT *
                FROM `".$base."`.`tmp_status_2`
                ;  
            ";
            $req = $connection_noa->prepare($strSql);

            if(!$req->execute()){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            
            $req->closeCursor();

            $object_retour->metro["etape3"] = getDateTimeWithMili();

            //--------------------------------------------------------------------------
            $strSql = "DROP TABLE IF EXISTS `tmp_data_last_statut`;
                CREATE TABLE `tmp_data_last_statut`
                (
                    `id_statut` int(11) NOT NULL,
                    `id_toc` int(11) NOT NULL,
                    `statut` tinyint(2) NOT NULL,
                    `dateTime_creation` dateTime,
                    `auteur_creation` dateTime,
                    INDEX  (`statut`),
                    INDEX  `id_toc_statut` (`id_toc`, `statut`)
                )
                SELECT e.*
                FROM (
                        SELECT c.*, CASE
                                WHEN (c.`cas1` = 1 OR `cas2` = 1) THEN SUBSTRING_INDEX(SUBSTRING_INDEX(c.`histo_id_statut`, ';', -4),';',1)
                                WHEN (c.`cas3` = 1 OR `cas4` = 1) THEN SUBSTRING_INDEX(SUBSTRING_INDEX(c.`histo_id_statut`, ';', -5),';',1)
                                ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(c.`histo_id_statut`, ';', -3),';',1)
                        END as 'last_statut_id'
                        FROM (
                                SELECT b.*
                                , IF(b.`histo_statut` LIKE '%;5;8;f;', 1, 0) as 'cas1'
                                , IF(b.`histo_statut` LIKE '%;5;4;f;', 1, 0) as 'cas2'
                                , IF(b.`histo_statut` LIKE '%;5;4;8;f;', 1, 0) as 'cas3'
                                , IF(b.`histo_statut` LIKE '%;5;8;4;f;', 1, 0) as 'cas4'
                                FROM (
                                        SELECT a.`id_toc`
                                        , CONCAT(';d;',GROUP_CONCAT(a.`statut` ORDER BY a.`dateTime_creation` ASC, a.`id_statut` asc SEPARATOR ';'),';f;') as 'histo_statut'
                                        , CONCAT(';d;',GROUP_CONCAT(a.`id_statut` ORDER BY a.`dateTime_creation` ASC, a.`id_statut` asc SEPARATOR ';'),';f;') as 'histo_id_statut'
                                        FROM `tmp_data_status` a
                                        WHERE 1=1
                                        GROUP BY a.`id_toc`
                                ) b
                        ) c
                ) d, `tmp_data_status` e
                WHERE 1=1
                AND d.`last_statut_id` = e.`id_statut`
            ;";
            $req = $connection->prepare($strSql);

            if(!$req->execute()){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            $req->closeCursor();

            $object_retour->metro["etape4"] = getDateTimeWithMili();
            
            //------------------------------------------------------------------
            $strSql = "DROP TABLE IF EXISTS `tmp_data_temps_passe`;

            CREATE TEMPORARY TABLE `temp_temps_passe_0`
            (
                    `id_toc` int(11) NOT NULL,
                    `statut` int(11) NOT NULL,
                    `dateTime_creation` dateTime,
                    INDEX  (`id_toc`)
            ) 
            SELECT a.`id_toc`, b.`statut`, MIN(b.`dateTime_creation`) as 'dateTime_creation'
            FROM `tmp_data_tickets` a
            LEFT OUTER JOIN `tmp_data_status` b
                    ON b.`id_toc` = a.`id_toc` 
                    AND b.`statut` = 1/*Nouveau*/
            WHERE 1=1
            GROUP BY a.`id_toc`
            ;

            CREATE TEMPORARY TABLE `temp_temps_passe_1`
            (
                    `id_toc` int(11) NOT NULL,
                    `statut` int(11) NOT NULL,
                    `dateTime_creation` dateTime,
                    INDEX  (`id_toc`)
            )
            SELECT a.`id_toc`, b.`statut`, MIN(b.`dateTime_creation`) as 'dateTime_creation'
            FROM `tmp_data_tickets` a
            LEFT OUTER JOIN `tmp_data_status` b
                    ON b.`id_toc` = a.`id_toc` 
                    AND b.`statut` = 2/*Nouveau*/
            WHERE 1=1
            GROUP BY a.`id_toc`
            ;

            CREATE TEMPORARY TABLE `temp_temps_passe_2`
            (
                    `id_toc` int(11) NOT NULL,
                    `statut` int(11) NOT NULL,
                    `dateTime_creation` dateTime,
                    INDEX  (`id_toc`)
            )
            SELECT a.`id_toc`, b.`statut`, MIN(b.`dateTime_creation`) as 'dateTime_creation'
            FROM `tmp_data_tickets` a
            LEFT OUTER JOIN `tmp_data_status` b
                    ON b.`id_toc` = a.`id_toc` 
                    AND b.`statut` = 5/*Nouveau*/
            WHERE 1=1
            GROUP BY a.`id_toc`
            ;

            CREATE TABLE `tmp_data_temps_passe`
            (
                    `id_toc` int(11) NOT NULL,
                    `arriver` dateTime,
                    `aquitter` dateTime,
                    `fermer` dateTime,
                    INDEX  (`id_toc`)
            )
            SELECT a.`id_toc`, b.`dateTime_creation` as 'arriver', c.`dateTime_creation` as 'aquitter', d.`dateTime_creation` as 'fermer'
            FROM `tmp_data_tickets` a
            LEFT OUTER JOIN `temp_temps_passe_0` b
            ON a.`id_toc` = b.`id_toc`
            LEFT OUTER JOIN `temp_temps_passe_1` c
            ON a.`id_toc` = c.`id_toc`
            LEFT OUTER JOIN `temp_temps_passe_2` d
            ON a.`id_toc` = d.`id_toc`
            ;
            ";
            $req = $connection->prepare($strSql);

            if(!$req->execute()){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            $req->closeCursor();
            
            $object_retour->metro["etape5"] = getDateTimeWithMili();
            
            //------------------------------------------------------------------
            $now = date("Y-m-d H:i:s");
            $strSql = "UPDATE `tab_indicateurs` a
                SET a.`param` = '".json_encode($p_param)."',
                    a.`buildDate` = '".$now."'
                WHERE 1=1
                AND a.`code` = '".$p_code."'
            ;";
            $req = $connection->prepare($strSql);
            if($req->execute() == false){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            $req->closeCursor();
        }
        
        $object_retour->metro["etape99"] = getDateTimeWithMili();
        
        return $object_retour;
    } catch (Exception $e) {
        $object_retour = new stdClass();
        $msg = $e->getMessage();
        $object_retour->strErreur = $msg;
        $object_retour->metro = "";
        $object_retour->data["recal"] = "ko";
        return $object_retour;
    }
}
/**
 * getIndicateur
 * @param string $p_prefixTable
 * @param int $p_code
 * @param int $p_duree
 * @param \stdClass $p_param
 * @return \stdClass
 */
function getIndicateur($p_connectionNOA, $p_prefixTable, $p_code, $p_duree, $p_param) {
    global $base;
    global $connection;
    global $connection_noa;
    global $prefixTable;
    
    try {     
        $object_retour = new stdClass();
        $object_retour->strErreur = "";
        $object_retour->data = "";
        
        //On vérifie si donnee stocké est bonne
        $strParam = addslashes(json_encode($p_param));
        
        $strSql = "SELECT a.*
            , IF(((a.`buildDate` + INTERVAL ".$p_duree." SECOND) > NOW()), true, false) as 'ajour'
            , IF( a.`param` = '".$strParam."', true, false) as 'sameParam'
            FROM `".$p_prefixTable."tab_indicateurs` a
            WHERE 1=1
            AND a.`code` = '".$p_code."'
        ;";

        $req = $connection->prepare($strSql);

        $rows = array();
        if($req->execute()){
            $rows = $req->fetch();
        }else{
            die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
        }
        $req->closeCursor();

        $indicateur = new stdClass();
        $indicateur->code = $p_code;
        $indicateur->data = $rows;
        
        //Si pas de donnée, ou si pas à jour, ou que les paramètres sont différent.
        if(($indicateur->data["data"] == "")||($indicateur->data["ajour"] == "0")||($indicateur->data["sameParam"] == "0")){
            //Met à jour les paramètres
            $strSql = $indicateur->data["def"];
            
            $subSql = substr($strSql,0,5);
            if($subSql == "proc:"){
                $proc = substr($strSql,5);
                $func = $proc;
                $indicateur = $func($p_connectionNOA,$p_prefixTable,$p_code,$p_param);
            }else{
                //cas d'une requête
                foreach($p_param as $key => $value) {
                    $strSql = str_replace("##".$key."##", $value, $strSql);
                }

                // On envois la requète
                if(!($select = $p_connectionNOA->query($strSql))){
                    var_dump($indicateur);
                    var_dump($p_param);  
                    die("Erreur SQL (3, code:".$p_code."), ".$strSql.'<br>'.print_r($p_connectionNOA->errorInfo(), true));
                }

                // On indique que nous utiliserons les résultats en tant qu'objet
                $select->setFetchMode(PDO::FETCH_OBJ);

                // On transforme les résultats en tableaux d'objet
                $indicateur->data["data"] = $select->fetchAll(PDO::FETCH_OBJ);
                $indicateur->data["param"] = $p_param;
                $indicateur->data["nombre"] = count($indicateur->data["data"]);
            }

            // On envois la requète (Suppression arbitraire de 2 enregistrements)
            $now = date("Y-m-d H:i:s");
            $indicateur->data["buildDate"] = $now;
            $strData = addslashes(json_encode($indicateur->data["data"]));
            $strParam = addslashes(json_encode($indicateur->data["param"]));
            
            $strSql = "UPDATE `tab_indicateurs` a
                SET a.`data` = '".$strData."',
                    a.`param` = '".$strParam."',
                    a.`nombre` = ".$indicateur->data["nombre"].",
                    a.`buildDate` = '".$now."'
                WHERE 1=1
                AND a.`code` = '".$p_code."'
            ;";
            $req = $connection->prepare($strSql);
            if(!($req->execute())){
                die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
            }
            $req->closeCursor();
        }else{
            $indicateur->data["data"] = json_decode($indicateur->data["data"]);
            $indicateur->data["param"] = json_decode($indicateur->data["param"]);
        }
        
        $object_retour->data = $indicateur;

        return $object_retour;
    } catch (Exception $e) {
            $object_retour = new stdClass();
            $msg = $e->getMessage();
            $object_retour->strErreur = "code:".$p_code.", msg:".$msg;
            $object_retour->strData = "";
            return $object_retour;
    }
}

/**
 * @name getSLAout
 * @desc conserve pour exemple d'appel à sub function
 * @param array $p_array
 * @param string $p_key
 * @return string
 */
function getSLAout($p_connectionNOA,$p_prefixTable,$p_code,$p_param) {
    try {
        $indicateur = new stdClass();
        $indicateur->code = $p_code;
        $indicateur->data["param"] = $p_param;
        $indicateur->data["data"] = "truc";
        $indicateur->data["nombre"] = 1;
        
        //----------------------------------------------------------------------
        $sql = "pour exemple
        ;";
        
        // On envois la requète
        $select = $p_connectionNOA->query($sql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($p_connectionNOA->errorInfo(), true));
        
        // On indique que nous utiliserons les résultats en tant qu'objet
        $select->setFetchMode(PDO::FETCH_OBJ);

        // On transforme les résultats en tableaux d'objet
        $indicateur->data["data"] = $select->fetchAll(PDO::FETCH_OBJ);
        $indicateur->data["nombre"] = count($indicateur->data["data"]);
                
        //----------------------------------------------------------------------
        
        return $indicateur;
    } catch (Exception $e) {
        $indicateur = new stdClass();
        $indicateur->code = $p_code;
        $indicateur->data = "";
        return $indicateur;
    }
}
?>